Preliminaries

Your application is gaining traction and the performance overhead of hosting both the application and the database on one server is becoming expensive. In this tutorial we’ll show you how to break free of the one-server setup and correctly configure your MariaDB installation so you can access it remotely.

Install MariaDB on your Database Server

We need to install MariaDB on our database server. The exact syntax on how to perform this task will vary between different Linux distributions. We will show the syntax for Ubuntu 14.04 but you should be able to get the same functionality if you replace ‘apt-get’ with the appropriate package manager for your distribution.

sudo apt-get update
sudo apt-get install mariadb-server libmariadbd-dev
sudo mysql_install_db

You will be prompted to set a root password. You should set one now and take careful note of it. Since we are deploying a production server, we want to make sure that we have secured our database. Luckily, MariaDB comes with a script to remove anonymous users and test databases. Run the following command:

sudo mysql_secure_installation

You will be prompted for your root password that you set previously. Enter it and accept all the prompts following the password. This will ensure that your database does not have any default security vulnerabilities.

Now we need to edit the main configuration file for MariaDB with sudo privilages

sudo nano /etc/mysql/my.cnf

In this file, find the section that begins with [mysqld]. You’ll see a field named ‘bind-address’. If you have enabled private networking on your web server and your database server, you should put your private IP address here. Otherwise your public IP address is fine. Now save and quit the configuration file and restart the mysql daemon.

sudo service mysql restart

Create Different Users and Privileges

We need to use the mysql prompt so have the root password you set during installation ready. Run the following command and you will be prompted for your password

mysql -u root -p

Once you’re logged in, you should be faced with a mysql prompt. For illustration purposes, we’ll create a new database named ‘testdb’.

CREATE DATABASE testdb;

We’ll be creating both a local user with all privileges granted and a remote user who only requires the privileges that our application will need. For example, if our application only requires read, write, and update privileges then we’ll only give it those privileges for security. Run the following command in the mysql prompt:

CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'type_a_nice_password_here';

You can replace testuser and the password field appropriately if you wish to continue using this user after the tutorial. Now that we’ve created the local user, we can grant it all privileges for testdb. We would have to run this command for every database for which we wish to give our user heightened privileges.

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

So now we’ve created a user ‘testuser’ that can only access the database from the local machine. But that’s not what we initially set out to do. Let’s create our remote user now that our web application will use to interact with the database.

CREATE USER 'webappuser'@'web_app_server_ip' IDENTIFIED BY 'password';

You should substitute the relevant IP and password fields appropriately. Continuing with the example from above — let’s suppose that our web app doesn’t require DELETE privileges. Then we’ll grant the ‘webappuser’ account only SELECT, INSERT, and UPDATE privileges.

GRANT SELECT,INSERT,UPDATE on testdb.* TO 'webappuser'@'web_app_server_ip';

To make the privilege changes take effect immediately, enter the following

FLUSH PRIVILEGES;

That’s it! We should be able to connect to the database locally using the ‘testuser’ user and remotely on our web app server using the ‘webappuser’ account. Run the following command on your database server:

mysql -u testuser -p

You’ll be prompted to enter the password for the local testuser you just created. If successful, you’ll be able to do whatever you’d like with the ‘testdb’ database. Now switch to your web app server and run the mysql command again — this time with the webappuser username. Note that you will need to have the MariaDB client (not server) installed on your web app server if you wish to interact with the remote server using the command line tool. Run sudo apt-get install mariadb-client to install the client.

mysql -u webappuser -p

Again, if everything worked correctly, you should be at the mysql prompt. You’ll only be able to read, write, and update (which is exactly what we wanted).

Final Words

Congratulations! You have a running database server on which you’re able to create remote/local users with appropriate access and grant privileges. You’ve taken another leap towards the stars by using the cloud for your database storage. Check out our Community Section on more information on database, server administration, and development tasks. From all of us at Stack Harbor, ahoy!